﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
#region using for special implements
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Reflection;
using Com.ProjectA.Utility;
#endregion using for special implements
namespace Com.ProjectA.DAC
{
    public class DACCommandManager
    {
        #region Declare variables
        
        #endregion Declare variables

        #region Local methods
        /// <summary>
        /// Get List Parameters In Store Procedure By Connection, Store Procedure name
        /// </summary>
        /// <param name="sqlConnection">DbConnection</param>
        /// <param name="strStoreProcedure">Store Procedure name</param>
        /// <returns>List Of Sql Parameter. Caution: It can be empty</returns>
        private List<SqlParameter> GetListParamsOfStoreProcedure(SqlConnection sqlConnection, string strStoreProcedure)
        {
            #region Declare variables
            List<SqlParameter> lsParam = new List<SqlParameter>();
            SqlConnection conn = null;
            SqlCommand cmd = null;
            #endregion Declare variables
            try
            {
                if (strStoreProcedure != null)
                {
                    conn = new SqlConnection(sqlConnection.ConnectionString);
                    cmd = new SqlCommand(strStoreProcedure, conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlCommandBuilder.DeriveParameters(cmd);

                    foreach (SqlParameter p in cmd.Parameters)
                    {
                        lsParam.Add(p);
                    }
                }
                return lsParam;
            }
            catch (Exception ex)
            {
                return lsParam;
            }
        }
        /// <summary>
        /// Mapping value from instance of T to command text
        /// </summary>
        /// <typeparam name="T">T type</typeparam>
        /// <param name="t">input object T</param>
        /// <param name="strCommandText">sql command text</param>
        /// <returns>string mapped. Caution: return can be null</returns>
        private string MappingValueToComamdText<T>(T t, string strCommandText)
        {
            StringBuilder strTemp = null;
            try
            {
                if (!String.IsNullOrEmpty(strCommandText))
                {
                    foreach (PropertyInfo pi in t.GetType().GetProperties())
                    {
                        strTemp = new StringBuilder();
                        strTemp.Append(Constants.STR_SPECIAL_CHAR_OPEN_BLOCK).Append(pi.Name)
                            .Append(Constants.STR_SPECIAL_CHAR_CLOSE_BLOCK);
                        Type type = pi.GetType();
                        strCommandText = strCommandText.Replace(strTemp.ToString(), pi.GetValue(t, null) == null ? String.Empty : pi.GetValue(t, null).ToString());
                    }
                }
                return strCommandText;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// Mapping value from data reader/data record to object
        /// </summary>
        /// <typeparam name="T">T type</typeparam>
        /// <param name="t">input object T</param>
        /// <param name="dataRecord">Data Record Of Data Reader</param>
        /// <returns>string mapped. Caution: return can be null</returns>
        private bool MappingDataReaderToObject<T>(ref T t, SqlDataReader dataRecord)
        {
            try
            {
                foreach (PropertyInfo pi in t.GetType().GetProperties())
                {
                    pi.SetValue(t, (dataRecord[pi.Name] == null ? null : dataRecord[pi.Name]), null);
                }
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        #endregion Local methods

        /// <summary>
        /// Execute non query with T type, dac connection manager, command type, command text or store procedure
        /// <para>T: Input T type</para>
        /// <para>dacConnectionManager: DAO Connection Manager</para>
        /// <para>commandType: CommandType (Store, command text, direct table)</para>
        /// <para>strCommandText: Command text</para>
        /// <para>strStoreProcedure: Store procedure</para>
        /// <para>returns: 1 - fail, 0 - success</para>
        /// </summary>
        /// <typeparam name="T">T type</typeparam>
        /// <param name="t">Input T type</param>
        /// <param name="dacConnectionManager">DAO Connection Manager</param>
        /// <param name="commandType">CommandType (Store, command text, direct table)</param>
        /// <param name="strCommandText">Command text</param>
        /// <param name="strStoreProcedure">Store procedure</param>
        /// <returns>integer - 0 is fail, 1 is success</returns>
        public int ExecuteNonQueryCommand<T>(T t, DACConnectionManager dacConnectionManager, CommandType commandType, string strCommandText = null, string strStoreProcedure = null)
        {
            #region Declare variables
            List<SqlParameter> lsParam = null;
            SqlCommand sqlCommand = null;
            int intResult = 0;
            #endregion Declare variables
            try
            {
                switch (commandType)
                {
                    case CommandType.StoredProcedure: // Store procedure
                        {
                            lsParam = GetListParamsOfStoreProcedure(dacConnectionManager.sqlConnection, strStoreProcedure);
                            if (lsParam.Count > 0)
                            {
                                sqlCommand = new SqlCommand(strStoreProcedure, dacConnectionManager.sqlConnection, dacConnectionManager.sqlTransaction);
                                sqlCommand.CommandType = commandType;
                                foreach (SqlParameter param in lsParam) 
                                {
                                    sqlCommand.Parameters.Add(param);
                                }
                                sqlCommand.Transaction = dacConnectionManager.sqlTransaction;
                                intResult = sqlCommand.ExecuteNonQuery();
                                sqlCommand.Transaction.Commit();
                            }
                            break;
                        }
                    default: // Command text
                        {
                            strCommandText = MappingValueToComamdText<T>(t, strCommandText);
                            if (strCommandText != null)
                            {
                                sqlCommand = new SqlCommand(strCommandText, dacConnectionManager.sqlConnection);
                                sqlCommand.CommandType = commandType;
                                sqlCommand.Transaction = dacConnectionManager.sqlTransaction;
                                
                                intResult = sqlCommand.ExecuteNonQuery();
                                sqlCommand.Transaction.Commit();
                            }
                            break;
                        }
                }
                return intResult;
            }
            catch (Exception ex)
            {
                if (sqlCommand != null)
                {
                    sqlCommand.Transaction.Rollback();
                }
                return 0;
            }
        }

        /// <summary>
        /// Execute Reader with T type, dac connection manager, command type, command text or store procedure
        /// <para>dacConnectionManager: DAO Connection Manager</para>
        /// <para>commandType: CommandType (Store, command text, direct table)</para>
        /// <para>strCommandText: Command text</para>
        /// <para>strStoreProcedure: Store procedure</para>
        /// <para>Return: SqlDataReader. Caution: Can be null</para>
        /// </summary>
        /// <param name="dacConnectionManager">DAO Connection Manager</param>
        /// <param name="commandType">CommandType (Store, command text, direct table)</param>
        /// <param name="strCommandText">Command text</param>
        /// <param name="strStoreProcedure">Store procedure</param>
        /// <returns>SqlDataReader. Caution: Can be null</returns>
        ///  

        public List<T> ExecuteReaderCommand<T>(DACConnectionManager dacConnectionManager, CommandType commandType, string strCommandText = null, string strStoreProcedure = null)
        {
            #region Declare variables
            List<SqlParameter> lsParam = null;
            SqlCommand sqlCommand = null;
            SqlDataReader sqlDataReader = null;
            List<T> lsObj = null;
            #endregion Declare variables
            try
            {
                switch (commandType)
                {
                    case CommandType.StoredProcedure: // Store procedure
                        {
                            lsParam = GetListParamsOfStoreProcedure(dacConnectionManager.sqlConnection, strStoreProcedure);
                            if (lsParam.Count > 0)
                            {
                                sqlCommand.CommandType = commandType;
                                foreach (SqlParameter param in lsParam)
                                {
                                    sqlCommand.Parameters.Add(param);
                                }
                                sqlDataReader = sqlCommand.ExecuteReader();
                            }
                            break;
                        }
                    default: // Command text
                        {
                            if (strCommandText != null)
                            {
                                sqlCommand = new SqlCommand(strCommandText, dacConnectionManager.sqlConnection);
                                sqlCommand.CommandType = commandType;
                                sqlDataReader = sqlCommand.ExecuteReader();
                            }
                            break;
                        }
                }
                //while (sqlDataReader.Read())
                //{
                    //if (lsObj == null)
                    //{
                    //    lsObj = new List<T>();
                    //}
                    //T t = Activator.CreateInstance<T>();
                    //if (MappingDataReaderToObject<T>(ref t, sqlDataReader))
                    //{
                    //    lsObj.Add(t);
                    //}
                //}
                
                //add Datatable to list
                DataTable dt = new DataTable();
                dt.Load(sqlDataReader);
                return dt.ToCollection<T>();
                
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// Execute Reader with T type, dac connection manager, command type, command text or store procedure
        /// <para>T: Input T type</para>
        /// <para>dacConnectionManager: DAO Connection Manager</para>
        /// <para>commandType: CommandType (Store, command text, direct table)</para>
        /// <para>strCommandText: Command text</para>
        /// <para>strStoreProcedure: Store procedure</para>
        /// <para>return T. Caution: Can be null</para>
        /// </summary>
        /// <typeparam name="T">T type</typeparam>
        /// <param name="t">Input T type</param>
        /// <param name="dacConnectionManager">DAO Connection Manager</param>
        /// <param name="commandType">CommandType (Store, command text, direct table)</param>
        /// <param name="strCommandText">Command text</param>
        /// <param name="strStoreProcedure">Store procedure</param>
        /// <returns>T. Caution: Can be null</returns>
        public T ExecuteReaderCommand<T>(T t, DACConnectionManager dacConnectionManager, CommandType commandType, string strCommandText = null, string strStoreProcedure = null)
        {
            #region Declare variables
            List<SqlParameter> lsParam = null;
            SqlCommand sqlCommand = null;
            SqlDataReader sqlDataReader = null;
            #endregion Declare variables
            try
            {
                switch (commandType)
                {
                    case CommandType.StoredProcedure: // Store procedure
                        {
                            lsParam = GetListParamsOfStoreProcedure(dacConnectionManager.sqlConnection, strStoreProcedure);
                            if (lsParam.Count > 0)
                            {
                                sqlCommand.CommandType = commandType;
                                foreach (SqlParameter param in lsParam)
                                {
                                    sqlCommand.Parameters.Add(param);
                                }
                                sqlDataReader = sqlCommand.ExecuteReader();
                            }
                            break;
                        }
                    default: // Command text
                        {
                            strCommandText = MappingValueToComamdText<T>(t, strCommandText);
                            if (strCommandText != null)
                            {
                                sqlCommand = new SqlCommand(strCommandText, dacConnectionManager.sqlConnection);
                                sqlCommand.CommandType = commandType;
                                sqlDataReader = sqlCommand.ExecuteReader();
                            }
                            break;
                        }
                }
                while (sqlDataReader.Read())
                {
                    if (MappingDataReaderToObject<T>(ref t, sqlDataReader))
                    {
                        return t;
                    }
                }
                return default(T);
            }
            catch (Exception ex)
            {
                return default(T);
            }
        }

        /// <summary>
        /// Execute Reader with T type, dac connection manager, command type, command text or store procedure
        /// <para>T: Input T type</para>
        /// <para>dacConnectionManager: DAO Connection Manager</para>
        /// <para>commandType: CommandType (Store, command text, direct table)</para>
        /// <para>strCommandText: Command text</para>
        /// <para>strStoreProcedure: Store procedure</para>
        /// <para>SqlDataReader. Caution: Can be null</para>
        /// </summary>
        /// <typeparam name="T">T type</typeparam>
        /// <param name="t">Input T type</param>
        /// <param name="dacConnectionManager">DAO Connection Manager</param>
        /// <param name="commandType">CommandType (Store, command text, direct table)</param>
        /// <param name="strCommandText">Command text</param>
        /// <param name="strStoreProcedure">Store procedure</param>
        /// <returns>SqlDataReader. Caution: Can be null</returns>
        public SqlDataReader ExecuteReaderCommandDataReader<T>(T t, DACConnectionManager dacConnectionManager, CommandType commandType, string strCommandText = null, string strStoreProcedure = null)
        {
            #region Declare variables
            List<SqlParameter> lsParam = null;
            SqlCommand sqlCommand = null;
            SqlDataReader sqlDataReader = null;
            #endregion Declare variables
            try
            {
                switch (commandType)
                {
                    case CommandType.StoredProcedure: // Store procedure
                        {
                            lsParam = GetListParamsOfStoreProcedure(dacConnectionManager.sqlConnection, strStoreProcedure);
                            if (lsParam.Count > 0)
                            {
                                sqlCommand.CommandType = commandType;
                                foreach (SqlParameter param in lsParam)
                                {
                                    sqlCommand.Parameters.Add(param);
                                }
                                sqlDataReader = sqlCommand.ExecuteReader();
                            }
                            break;
                        }
                    default: // Command text
                        {
                            strCommandText = MappingValueToComamdText<T>(t, strCommandText);
                            if (strCommandText != null)
                            {
                                sqlCommand = new SqlCommand(strCommandText, dacConnectionManager.sqlConnection);
                                sqlCommand.CommandType = commandType;
                                sqlDataReader = sqlCommand.ExecuteReader();
                            }
                            break;
                        }
                }
                return sqlDataReader;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// Execute Scalar with T type, dac connection manager, command type, command text or store procedure
        /// <para>T: Input T type</para>
        /// <para>dacConnectionManager: DAO Connection Manager</para>
        /// <para>commandType: CommandType (Store, command text, direct table)</para>
        /// <para>outObj: Out Object - Integer. 0 is fail or error</para>
        /// <para>strCommandText: Command text</para>
        /// <para>strStoreProcedure: Store procedure</para>
        /// <para>return Integer.</para>
        /// </summary>
        /// <typeparam name="T">T type</typeparam>
        /// <param name="t">Input T type</param>
        /// <param name="dacConnectionManager">DAO Connection Manager</param>
        /// <param name="commandType">CommandType (Store, command text, direct table)</param>
        /// <param name="outObj">Out Object - Integer. 0 is fail or error</param>
        /// <param name="strCommandText">Command text</param>
        /// <param name="strStoreProcedure">Store procedure</param>
        /// <returns>Integer.</returns>
        public string ExecuteScalarCommand<T>(T t, DACConnectionManager dacConnectionManager, CommandType commandType, out int outObj, string strCommandText = null, string strStoreProcedure = null)
        {
            #region Declare variables
            List<SqlParameter> lsParam = null;
            SqlCommand sqlCommand = null;
            object outObject = null;
            #endregion Declare variables
            try
            {
                switch (commandType)
                {
                    case CommandType.StoredProcedure: // Store procedure
                        {
                            lsParam = GetListParamsOfStoreProcedure(dacConnectionManager.sqlConnection, strStoreProcedure);
                            if (lsParam.Count > 0)
                            {
                                sqlCommand.CommandType = commandType;
                                foreach (SqlParameter param in lsParam)
                                {
                                    sqlCommand.Parameters.Add(param);
                                }
                                outObject = sqlCommand.ExecuteScalar();
                            }
                            break;
                        }
                    default: // Command text
                        {
                            strCommandText = MappingValueToComamdText<T>(t, strCommandText);
                            if (strCommandText != null)
                            {
                                sqlCommand = new SqlCommand(strCommandText, dacConnectionManager.sqlConnection);
                                sqlCommand.CommandType = commandType;
                                outObject = sqlCommand.ExecuteScalar();
                            }
                            break;
                        }
                }
                if (outObject == null)
                {
                    outObj = 0;
                    return "Out Object Is Null";
                }
                outObj = Convert.ToInt32(outObject);
                return "Get Max Ok!";
            }
            catch (Exception ex)
            {
                outObj = -1;
                return "Error: " + ex.Message;
            }
        }

       
    }
}

//Add Data table to list collection
public static class MyExtensionClass
{
    public static List<T> ToCollection<T>(this DataTable dt)
    {
        List<T> lst = new System.Collections.Generic.List<T>();
        Type tClass = typeof(T);
        PropertyInfo[] pClass = tClass.GetProperties();
        List<DataColumn> dc = dt.Columns.Cast<DataColumn>().ToList();
        T cn;
        foreach (DataRow item in dt.Rows)
        {
            cn = (T)Activator.CreateInstance(tClass);
            foreach (PropertyInfo pc in pClass)
            {
                // Can comment try catch block. 
                try
                {
                    DataColumn d = dc.Find(c => c.ColumnName == pc.Name);
                    if (d != null)
                        pc.SetValue(cn, item[pc.Name], null);
                }
                catch
                {
                }
            }
            lst.Add(cn);
        }
        return lst;
    }
}
